<?php

namespace app\cron\controller;

use app\BaseController;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;
use think\facade\Db;

class SqlClean extends BaseController
{
    /**
     * 清除队伍表中的无效数据
     * @return void
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function projectZrr()
    {
        // 清空无父孤儿
        no_parent:
        $data = Db::table('t_project_zrr')
            ->field(['id', 'parent_id'])
            ->select();

        if (empty($data)) goto no_project;

        $data = $data->toArray();

        $id_array = array_column($data, 'id');
        $parent_id_array = array_column($data, 'parent_id');

        $diff = array_diff($parent_id_array, $id_array);
        $diff = array_filter($diff);
        $diff = array_unique($diff);
        if (empty($diff)) goto no_project;

        Db::table('t_project_zrr')
            ->where('parent_id', 'in', $diff)
            ->delete();


        // 清空无项目队伍
        no_project:
        $zrr = Db::table('t_project_zrr')
            ->field(['id', 'project_id'])
            ->select();
        if (empty($zrr)) goto end;
        $zrr = $zrr->toArray();
        $zrr_project_id_array = array_column($zrr, 'project_id');
        $zrr_project_id_array = array_unique($zrr_project_id_array);

        $project = Db::table('t_project')
            ->field(['id', 'project_name'])
            ->where('id', 'in', $zrr_project_id_array)
            ->select();

        if (empty($project)) goto project_name_sync;
        $project = $project->toArray();
        $project_id_array = array_column($project, 'id');
        $project_id_array = array_unique($project_id_array);

        $diff = array_diff($zrr_project_id_array, $project_id_array);
        $diff = array_filter($diff);
        $diff = array_unique($diff);
        if (empty($diff)) goto project_name_sync;

        Db::table('t_project_zrr')
            ->where('project_id', 'in', $diff)
            ->delete();

        // 同步队伍的项目名称
        project_name_sync:
        $zrr = Db::table('t_project_zrr')
            ->field(['id', 'project_id'])
            ->group('project_id')
            ->select();
        $zrr = $zrr->toArray();
        $zrr_project_id_array = array_column($zrr, 'project_id');

        $project = Db::table('t_project')
            ->where('id', 'in', $zrr_project_id_array)
            ->field(['id', 'project_name'])
            ->select();
        $project = $project->toArray();
        $project_array = array_column($project, null, 'id');

        foreach ($zrr_project_id_array as $v) {
            if (empty($project_array[$v])) continue;
            if (empty($project_array[$v]['project_name'])) continue;

            Db::table('t_project_zrr')
                ->where('project_id', $v)
                ->update(['project_name' => $project_array[$v]['project_name']]);
        }

        // 清理完毕
        end:
        $this->success('执行完毕');
    }
}